﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL.Entity;
namespace ECS.DAL.Component
{
    public class ZoneLocationComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = "SELECT [zone_location_id], zl.[zone_id], zl.[location_id], zl.[is_active], zl.[create_date] " +
                                ",zl.[create_by] , z.[zone], l.[location] " +
                                " FROM [dbo].[t_eics_zone_location] zl" +
                                " inner join t_eics_zone z ON zl.[zone_id] = z.[zone_id] " +
                                " inner join t_eics_location l on zl.[location_id] = l.[location_id] order by l.[location] ";

        private const string SQL_PROC_QUERY = "";

        private const string SQL_QUERY_UNCOUPLE = "SELECT [zone_location_id], z.[zone_id], l.[location_id], zl.[is_active], zl.[create_date] " +
                                        ",zl.[create_by] , z.[zone], l.[location] " +
                                        " FROM t_eics_location l" +
                                        " left join [dbo].[t_eics_zone_location] zl ON l.[location_id]  = zl.[location_id] " +
                                        " left join t_eics_zone z on zl.[zone_id] = z.[zone_id] order by l.[location] ";

        private const string SQL_INSERT = "";

        private const string SQL_PROC_INSERT = "usp_InsertZoneLocationRecord " +
                                        "@in_vchApplicationID = [@AppId], @in_vchDevice = [@Device], @in_vchUserID = [@UserId] " +
                                        ",@in_vchZoneID  = [@ZoneId] " +
                                        ",@in_vchLocationID = [@LocationId] " +
                                        ",@in_vchIsActive	 = [@IsActive]";


        private const string SQL_UPDATE = "";

        private const string SQL_PROC_UPDATE = "usp_UpdateZoneLocationRecord " +
                                        "@in_vchApplicationID = [@AppId], @in_vchDevice = [@Device], @in_vchUserID = [@UserId] " +
                                        ",@in_vchZoneLocationID = [@ZoneLocationId] " +
                                        ",@in_vchZoneID  = [@ZoneId] " +
                                        ",@in_vchLocationID = [@LocationId] " +
                                        ",@in_vchIsActive	 = [@IsActive]";

        private const string SQL_DELETE = "";

        private const string SQL_PROC_DELETE = "usp_DeleteZoneLocationRecord @in_vchApplicationID = [@AppId], @in_vchDevice = [@Device]," +
                                            "@in_vchUserID = [@UserId], @in_vchZoneLocationID = [@ZoneLocationId], " +
                                            "@in_vchIsActive = [@IsActive]";

        private const string dataTableName = "ZoneLocation";
        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public ZoneLocationComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods

        #endregion

        #region public mehods
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            StringBuilder sqlString = new StringBuilder();
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            ZoneLocation param = null;
            param = parameter == null ? null : (ZoneLocation)parameter;
            using (Entities entities = new Entities())
            {
                entities.Open();
                sqlString.Append(SQL_QUERY);
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.Text;
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ZoneLocation data = new ZoneLocation();
                    data.ZoneLocationId = base.GetString(0, reader);
                    data.ZoneId = base.GetString(1, reader);
                    data.LocationId = base.GetString(2, reader);
                    data.IsActive = base.GetString(3, reader);
                    data.CreateDate = (DateTime)base.GetDate(4, reader);
                    data.CreateBy = base.GetString(5, reader);
                    data.ZoneName = base.GetString(6, reader);
                    data.LocationName = base.GetString(7, reader);
                    data.NotChangeObject();
                    items.Add(data);
                }
                /*
                 "SELECT [zone_location_id], [zone_id], [location_id], [is_active], [create_date] " +
                                ",[create_by] , z.[zone], l.[location] " +
                                " FROM [dbo].[t_eics_zone_location] zl" +
                                " left join t_eics_zone z ON zl.[zone_id] = z.[zone_id] " +
                                " left join t_eics_location l on zl.[location_id] = l.[location_id] ";
                 */
            }
            return items;
        }

        public List<ObjectContext> SearchUnCouple()
        {
            StringBuilder sqlString = new StringBuilder();
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            using (Entities entities = new Entities())
            {
                entities.Open();
                sqlString.Append(SQL_QUERY_UNCOUPLE);
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.Text;
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ZoneLocation data = new ZoneLocation();
                    data.ZoneLocationId = base.GetString(0, reader);
                    data.ZoneId = base.GetString(1, reader);
                    data.LocationId = base.GetString(2, reader);
                    data.IsActive = base.GetString(3, reader);
                    data.CreateDate = reader[4] == DBNull.Value ? new DateTime(0) : (DateTime)base.GetDate(4, reader);
                    data.CreateBy = base.GetString(5, reader);
                    data.ZoneName = base.GetString(6, reader);
                    data.LocationName = base.GetString(7, reader);
                    if (string.IsNullOrEmpty(data.ZoneId))
                    {
                        data.AddObject();
                    }
                    else
                    { data.NotChangeObject(); }
                    items.Add(data);
                }
            }
            return items;
        }

        public override string GetInsertSQL(ObjectContext param)
        {
            ZoneLocation parameter = (ZoneLocation)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserId]", BaseSQLModel.SqlWithQoute(parameter.UserId));
            sqlString.Replace("[@ZoneId]", BaseSQLModel.SqlWithQoute(parameter.ZoneId));
            sqlString.Replace("[@LocationId]", BaseSQLModel.SqlWithQoute(parameter.LocationId));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));
            return sqlString.ToString();
            /*
             ""@in_vchApplicationID = [@AppId], @in_vchDevice = [@Device], @in_vchUserID = [@UserId] " +
                                        ",@in_vchZoneLocationID = [@ZoneLocationId] " +
                                        ",@in_vchZoneID  = [@ZoneId] " +
                                        ",@in_vchLocationID = [@LocationId] " +
                                        ",@in_vchIsActive	 = [@IsActive]";
             */
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            ZoneLocation parameter = (ZoneLocation)param;
            StringBuilder sqlString = new StringBuilder();
            sqlString.Append(SQL_PROC_UPDATE);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserId]", BaseSQLModel.SqlWithQoute(parameter.UserId));
            sqlString.Replace("[@ZoneLocationId]", BaseSQLModel.SqlWithQoute(parameter.ZoneLocationId));
            sqlString.Replace("[@ZoneId]", BaseSQLModel.SqlWithQoute(parameter.ZoneId));
            sqlString.Replace("[@LocationId]", BaseSQLModel.SqlWithQoute(parameter.LocationId));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            ZoneLocation parameter = (ZoneLocation)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_DELETE);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserId]", BaseSQLModel.SqlWithQoute(parameter.UserId));
            sqlString.Replace("[@ZoneLocationId]", BaseSQLModel.SqlWithQoute(parameter.ZoneLocationId));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {

            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("ZoneLocation insert unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("zoneLocation update unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            ErrorMessage.Status = string.Empty;
            ErrorMessage.ErrorCode = string.Empty;
            ErrorMessage.Message = string.Empty;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    DataSet ds = new DataSet();
                    DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                    cmd.CommandType = CommandType.Text;
                    adapter.Fill(ds, dataTableName);
                    if (ds.Tables.Count > 0 && ds.Tables[dataTableName].Rows.Count > 0)
                    {
                        if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                        {
                            ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                            ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                            ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                            throw new Exception(ErrorMessage.Message);
                        }
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// special case for store procedure to update data that return result as record
        /// </summary>
        /// <param name="items"></param>
        public override void SaveChanges(List<ObjectContext> items)
        {
            string whMasterId = string.Empty;
            ZoneLocation parameter = null;
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand(sqlCmd);
                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    cmd.CommandType = CommandType.StoredProcedure;
                    foreach (ObjectContext item in items)
                    {
                        parameter = (ZoneLocation)item;
                        if (string.IsNullOrEmpty(parameter.ZoneId) && parameter.GetObjectSate() == OBJECT_STATE.Insert)
                        { continue; }
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = GetInsertSQL(item);
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sqlCmd;
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sqlCmd;
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sqlCmd;
                                textMessage = "Delete ";
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            adapter.SelectCommand = cmd;
                            adapter.Fill(ds, dataTableName);
                            if (ds.Tables.Count > 0 && ds.Tables[dataTableName].Rows.Count > 0)
                            {
                                if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                                {
                                    ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                                    ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                                    ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                                    throw new Exception(ErrorMessage.Message);
                                }
                            }
                            //item.NotChangeObject();
                        }
                    }

                }
                transaction.Complete();
                items.RemoveAll(o => o.ObjectStatus == OBJECT_STATE.Delete);
                foreach (ObjectContext item in items)
                {
                    item.NotChangeObject();
                }
            }
        }
        #endregion

    }
}
